import code
import os
import re
import pandas as pd
import pymysql
import sys
from datetime import datetime, timedelta
import json

def convert_net_inflow_to_float(main_net_inflow):
    if '万亿' in main_net_inflow:
        return float(main_net_inflow.strip().replace('万亿', '')) * 10000
    elif '亿' in main_net_inflow:
        return float(main_net_inflow.strip().replace('亿', ''))
    elif '万' in main_net_inflow:
        return float(main_net_inflow.strip().replace('万', '')) / 10000
    else:
        return float(main_net_inflow)

# 板块业务筛选算法主函数
# 算法共分4大步骤，此为框架，第一步待补充
def plate_business_filter_proc(conn, last_date=None):
    """板块业务筛选算法"""
    latest_date = ''
    plates = []
    try:
        with conn.cursor() as cursor:
            if last_date:
                latest_date = last_date
            else:
                # 获取最新交易日
                cursor.execute("SELECT MAX(trade_date) as latest_date FROM a_share_trading")
                latest_date_result = cursor.fetchone()
                if not latest_date_result['latest_date']:
                    print("未找到交易数据，无法执行剔除操作。")
                    return
                latest_date = latest_date_result['latest_date']

             # 获取所有板块名称
            cursor.execute("SELECT DISTINCT name FROM a_share_trading")
            plates = [row['name'] for row in cursor.fetchall()]

        print(f'开始计算日期为：{latest_date} 的数据')
        # 第一步：长期低迷且无改善板块剔除
        step1_kickoff_low_performance_plates(conn, latest_date, plates)

        # 第二步：“昨日前20板块”差异化处理
        step2_kickoff_high_growth_plates(conn, latest_date)
        
        # 第三步：“历史轮动排除周期”剔除
        # step3_kickoff_rotation_exclusion(conn, latest_date, plates)
        
        # 第四步：
        step4_score_and_select_plates(conn, latest_date)
        
    except Exception as e:
        print(f"板块业务筛选算法执行出错，行号 {sys._getframe().f_lineno}: {str(e)}")


def step1_kickoff_low_performance_plates(conn, latest_date, plates):
    try:
        with conn.cursor() as cursor:

            # 获取近15天的交易日列表
            cursor.execute("SELECT DISTINCT trade_date FROM a_share_trading WHERE trade_date <= %s ORDER BY trade_date DESC LIMIT 15", (latest_date,))
            trade_dates = [row['trade_date'] for row in cursor.fetchall()]
            if len(trade_dates) < 1:
                print("交易数据不足1天，无法执行剔除操作。")
                return

            # 获取近5天的交易日列表
            recent_5_dates = trade_dates[:5]
            # 获取总板块数
            cursor.execute("SELECT COUNT(DISTINCT name) as total_plates FROM a_share_trading WHERE trade_date = %s", (latest_date,))
            total_plates_result = cursor.fetchone()
            total_plates = total_plates_result['total_plates']
            threshold = int(total_plates * 0.8)

            # 获取近15天的交易数据
            placeholders_15_days = ','.join(['%s'] * len(trade_dates))
            cursor.execute(f"SELECT name, trade_date, daily_gain, rank1 FROM (SELECT name, trade_date, daily_gain, RANK() OVER (PARTITION BY trade_date ORDER BY CAST(REPLACE(daily_gain, '%%%%', '') AS DECIMAL(10,2)) DESC) as rank1 FROM a_share_trading WHERE trade_date IN ({placeholders_15_days})) sub", trade_dates)
            trading_data_15_days = cursor.fetchall()

            # 获取近5天的资金流向数据
            placeholders_5_days = ','.join(['%s'] * len(recent_5_dates))
            cursor.execute(f"SELECT name, flow_date, main_net_inflow FROM a_share_fund_flow WHERE flow_date IN ({placeholders_5_days})", recent_5_dates)
            fund_flow_data_5_days = cursor.fetchall()

            for plate in plates:
                # 筛选当前板块的近15天交易数据
                plate_trading_data_15_days = [row for row in trading_data_15_days if row['name'] == plate]
                # 筛选当前板块的近5天资金流向数据
                plate_fund_flow_data_5_days = [row for row in fund_flow_data_5_days if row['name'] == plate]

                # 计算指标A(最近15天内涨幅靠后20%的次数)
                index_a = sum(1 for row in plate_trading_data_15_days if row['rank1'] >= threshold)

                # 计算指标B(最近5天内总净流入)
                index_b = 0
                for row in plate_fund_flow_data_5_days:
                    main_net_inflow = row['main_net_inflow']
                    index_b += convert_net_inflow_to_float(main_net_inflow)

                # 计算例外条件：近5天内有3天涨幅排名在TOP20
                low_rank_days = sum(1 for row in plate_trading_data_15_days if row['trade_date'] in recent_5_dates and row['rank1'] < threshold)

                # 根据指标 A、指标 B 和近 5 天低排名天数决定板块是否剔除或保留
                if index_a >= 12:
                    if index_b <= 0 and not (low_rank_days >= 3 and index_b > 10):
                        # 剔除该板块
                        cursor.execute(
                            "UPDATE a_share_trading SET is_low_performance_eliminate = '是', is_reserved_in_pool = '否' WHERE name = %s",
                            (plate,)
                        )
                    elif low_rank_days >= 3 and index_b > 10:
                        # 例外保留该板块
                        cursor.execute(
                            "UPDATE a_share_trading SET is_low_performance_eliminate = '否', is_reserved_in_pool = '是' WHERE name = %s",
                            (plate,)
                        )
                elif index_a > 0 and len(recent_5_dates) <= 2 :
                        # 如果数据量太小，只要该板块涨幅是靠后的20% ，直接剔除该板块
                        cursor.execute(
                            "UPDATE a_share_trading SET is_low_performance_eliminate = '是', is_reserved_in_pool = '否' WHERE name = %s",
                            (plate,)
                        )

            conn.commit()
    except Exception as e:
        print(f"执行第一步长期低迷板块剔除时出错，行号 {sys._getframe().f_lineno}: {str(e)}")
        conn.rollback()


def step2_kickoff_high_growth_plates(conn, latest_date):
    try:
        with conn.cursor() as cursor:

            # 获取涨幅TOP20的板块数据
            cursor.execute("SELECT name, daily_gain FROM a_share_trading WHERE trade_date = %s ORDER BY CAST(REPLACE(daily_gain, '%%', '') AS DECIMAL(10,2)) DESC LIMIT 20", (latest_date,))
            top_20_plates = cursor.fetchall()

            # 获取这些板块的主力净流入数据
            plate_names = [plate['name'] for plate in top_20_plates]
            placeholders = ','.join(['%s'] * len(plate_names))
            cursor.execute(f"SELECT name, main_net_inflow FROM a_share_fund_flow WHERE name IN ({placeholders}) AND flow_date = '{latest_date}'", plate_names)
            fund_flow_data = cursor.fetchall()

            # 处理数据，根据主力净流入更新标记
            for flow_data in fund_flow_data:
                name = flow_data['name']
                main_net_inflow = flow_data['main_net_inflow']
                value = convert_net_inflow_to_float(main_net_inflow)

                if value < -5:
                    cursor.execute(
                        f"UPDATE a_share_trading SET is_low_performance_eliminate = '是', is_reserved_in_pool = '否' WHERE name = '{name}' AND trade_date = '{latest_date}'"
                    )
                elif value > 3:
                    cursor.execute(
                        f"UPDATE a_share_trading SET is_low_performance_eliminate = '否', is_reserved_in_pool = '是' WHERE name = '{name}' AND trade_date = '{latest_date}'"
                    )

            conn.commit()
    except Exception as e:
        print(f"执行第二步高增长板块筛选时出错，行号 {sys._getframe().f_lineno}: {str(e)}")
        conn.rollback()


def step3_kickoff_rotation_exclusion(conn, latest_date, plates):

    try:
        with conn.cursor() as cursor:


            date_object = datetime.strptime(latest_date, '%Y-%m-%d')
            # 计算一年前的日期
            one_year_ago = date_object.replace(year=date_object.year - 1).strftime('%Y-%m-%d')

            for plate in plates:
                # 获取过去1年该板块所有进入前20的日期
                cursor.execute(f"""SELECT DISTINCT trade_date
                                FROM (
                                    SELECT 
                                        trade_date,
                                        RANK() OVER (PARTITION BY trade_date ORDER BY CAST(REPLACE(daily_gain, '%%', '') AS DECIMAL(10,2)) DESC) as rank_num
                                    FROM a_share_trading
                                    WHERE name = '{plate}' AND trade_date BETWEEN '{one_year_ago}' AND '{latest_date}'
                                ) sub
                                WHERE rank_num <= 20
                                ORDER BY trade_date DESC""")
                top_20_dates = [row['trade_date'] for row in cursor.fetchall()]

                # 一年至少得有4次进入TOP20
                if len(top_20_dates) < 7: 
                    continue

                # 计算相邻间隔
                intervals = []
                for i in range(len(top_20_dates) - 1):
                    intervals.append((datetime.strptime(top_20_dates[i], "%Y-%m-%d") - datetime.strptime(top_20_dates[i+1], "%Y-%m-%d")).days)

                # 计算自身平均间隔
                avg_interval = sum(intervals) / (len(top_20_dates) - 1)

                # 确定排除周期
                if avg_interval < 5:
                    exclude_days = 3
                else:
                    exclude_days = avg_interval - 2

                # 计算排除日期
                exclude_date = (datetime.strptime(top_20_dates[0], "%Y-%m-%d") - pd.Timedelta(days=exclude_days)).strftime('%Y-%m-%d')

                # 检查近exclude_days天内该板块是否进入过前20
                cursor.execute("""SELECT 1 FROM (
                                    SELECT 
                                        name,
                                        trade_date,
                                        RANK() OVER (PARTITION BY trade_date 
                                                    ORDER BY CAST(REPLACE(daily_gain, '%%', '') AS DECIMAL(10,2)) DESC) AS `rank`
                                    FROM a_share_trading
                                ) ranked
                                WHERE 
                                    name = %s 
                                    AND trade_date >= %s 
                                    AND `rank` <= 20 
                                LIMIT 1;""", 
                                (plate, exclude_date))
                if cursor.fetchone():
                    cursor.execute(
                        "UPDATE a_share_trading SET is_low_performance_eliminate = '是', is_reserved_in_pool = '否' WHERE name = %s AND trade_date = %s",
                        (plate, latest_date)
                    )

            conn.commit()
    except Exception as e:
        import traceback
        print(f"{str(e)}")
        traceback.print_exc()
        raise


def check_previous_day_hits(last_date, conn, cursor):
    """
    计算前一日的推荐板块是否命中了今日的TOP20涨幅的板块
    查询前一日推荐为是的板块，然后再用这些板块在今日涨幅前20%的板块中匹配
    如果匹配成功，则将前一日的这个板块的hit_status状态写为已命中，
    没有匹配到的推荐板块的hit_status字段写为未命中
    """
    try:
        # 从 time_object 表获取前一天的交易日期
        cursor.execute("SELECT MAX(trading_date) as previous_day FROM time_object WHERE trading_date < %s", (last_date,))
        previous_day_result = cursor.fetchone()
        if not previous_day_result['previous_day']:
            print('未找到前一天的交易数据，无法计算命中率')
            return
        previous_day = previous_day_result['previous_day']
    except Exception as e:
        print(f'获取前一天交易日期时出错: {str(e)}')
        return

    # 获取前一日推荐为是的板块
    get_recommended_sql = "SELECT name FROM a_share_trading WHERE is_recommended = '是' AND trade_date = %s"
    cursor.execute(get_recommended_sql, (previous_day,))
    previous_day_recommended = [row['name'] for row in cursor.fetchall()]

    if not previous_day_recommended:
        return

    # 获取今日涨幅前20个板块
    get_top20_sql = "SELECT name FROM a_share_trading WHERE trade_date = %s ORDER BY daily_gain DESC LIMIT 20"
    cursor.execute(get_top20_sql, (last_date,))
    top20_plates = [row['name'] for row in cursor.fetchall()]

    # 更新匹配和未匹配的板块状态
    hit_count = 0
    for plate in previous_day_recommended:
        hit_status = '已命中' if plate in top20_plates else '未命中'
        if hit_status == '已命中':
            hit_count += 1
        update_sql = "UPDATE a_share_trading SET hit_status = %s WHERE name = %s AND trade_date = %s"
        cursor.execute(update_sql, (hit_status, plate, previous_day))

    # 计算命中率
    if previous_day_recommended:
        hit_rate = hit_count / len(previous_day_recommended)
    else:
        hit_rate = 0

    # 将命中率写入 time_object 表
    update_hit_rate_sql = "UPDATE time_object SET hit_rate = %s WHERE trading_date = %s"
    cursor.execute(update_hit_rate_sql, (hit_rate, previous_day))


def step4_score_and_select_plates(conn, latest_date):
    """
    第四步：对筛选后剩余的板块进行多维度（资金、事件、估值）打分，总分前25名进入待选池
    :param conn: 数据库连接
    :param latest_date: 目标日期
    """
    try:
        
        with conn.cursor() as cursor:
            # 获取筛选后剩余的板块（基于a_share_trading表）
            select_sql = "SELECT name FROM a_share_trading WHERE (is_reserved_in_pool IS NULL or  is_reserved_in_pool = '是') AND trade_date = %s"
            cursor.execute(select_sql, (latest_date,))
            remaining_plates = [row['name'] for row in cursor.fetchall()]

            # 1. 资金维度
            # calculate_and_update_fund_score(remaining_plates, latest_date, cursor)
            calculate_and_update_fund_score_v2(remaining_plates, latest_date, cursor)

            # 2. 事件维度打分存库
            calculate_update_event_score(remaining_plates, latest_date, cursor)

            # 3. 估值维度打分（基于a_share_trading表的市盈率pe_ratio）
            calculate_valuation_score(remaining_plates, latest_date, cursor)
            
            # 4. 根据牛市、熊市、震荡市选择加权系数
            fund_weight,event_weight,valuation_weight = calculate_weighted_coefficient(latest_date, cursor)

            # 5. 计算总分
            calculate_total_score(fund_weight,event_weight,valuation_weight,remaining_plates,latest_date, cursor)
            conn.commit()
            
            # 6. 按总分排序，取前25名
            # 获取各板块的总分
            plate_scores = {} 
            if remaining_plates:
                placeholders = ', '.join(['%s'] * len(remaining_plates))
                query = f"SELECT name, total_score FROM a_share_trading WHERE name IN ({placeholders}) AND trade_date = %s" 
                cursor.execute(query, (*remaining_plates, latest_date))
                results = cursor.fetchall()
                # 初始化所有板块分数为0
                for plate in remaining_plates:
                    plate_scores[plate] = 0
                # 使用查询结果更新分数
                for row in results:
                    plate_scores[row['name']] = row['total_score']

            # 7. 将板块按总分从高到低排序，取前25名板块，作为推荐板块
            sorted_plates = sorted(plate_scores.items(), key=lambda item: item[1], reverse=True)
            selected_plates = [plate[0] for plate in sorted_plates[:25]]

            update_sql = f"UPDATE a_share_trading SET is_recommended = '是' WHERE name IN ({{}}) and trade_date = '{{}}'"
            placeholders = ', '.join(['%s'] * len(selected_plates))
            full_update_sql = update_sql.format(placeholders, latest_date)
            cursor.execute(full_update_sql, selected_plates)
            conn.commit()

            check_previous_day_hits(latest_date, conn, cursor)
            conn.commit()
            
            return selected_plates
    except Exception as e:
        import sys
        print(f"Error in step4_score_and_select_plates, line {sys._getframe().f_lineno}: {str(e)}")
        raise

 
def calculate_total_score(fund_weight,event_weight,valuation_weight,remaining_plates,latest_date, cursor):
    """
    将各板块的总分写入 a_share_trading 表的 total_score 字段。    
    :param plate_scores: 包含各板块总分的字典
    :param latest_date: 交易日期
    :param cursor: 数据库游标
    """
    try:
        plate_scores = {}
        for plate in remaining_plates:
            fund_score_query = cursor.execute("SELECT fund_score_base FROM a_share_trading WHERE name = %s AND trade_date = %s", (plate, latest_date))
            fund_score = cursor.fetchone()['fund_score_base'] if fund_score_query else 0
            event_score_query = cursor.execute("SELECT event_score_base FROM a_share_trading WHERE name = %s AND trade_date = %s", (plate, latest_date))
            event_score = cursor.fetchone()['event_score_base'] if event_score_query else 0
            valuation_score_query = cursor.execute("SELECT valuation_score_base FROM a_share_trading WHERE name = %s AND trade_date = %s", (plate, latest_date))
            valuation_score = cursor.fetchone()['valuation_score_base'] if valuation_score_query else 0
            plate_scores[plate] = float(fund_weight) * float(fund_score) + float(event_weight) * float(event_score) + float(valuation_weight) * float(valuation_score) 
    
        for plate, total_score in plate_scores.items():
            update_sql = "UPDATE a_share_trading SET total_score = %s WHERE name = %s AND trade_date = %s"
            cursor.execute(update_sql, (total_score, plate, latest_date))
    except Exception as e:
        print(f"{str(e)}")
        raise

def calculate_net_inflow(remaining_plates, latest_date, cursor):
    """
    计算板块近3天的主力净流入
    :param remaining_plates: 剩余板块列表
    :param latest_date: 最新日期
    :param cursor: 数据库游标
    :return: 各板块的主力净流入字典
    """
    plate_flowin = {plate: 0 for plate in remaining_plates}
    three_days_ago = datetime.strptime(latest_date, '%Y-%m-%d') - timedelta(days=3)
    capital_sql = "SELECT name, main_net_inflow, total_market_cap, flow_date FROM a_share_fund_flow WHERE %s <= flow_date and flow_date <= %s "
    cursor.execute(capital_sql, (three_days_ago.strftime('%Y-%m-%d'), latest_date.format('%Y-%m-%d')))
    capital_scores = cursor.fetchall()
    for item in capital_scores:
        if item['name'] in plate_flowin:
            plate_flowin[item['name']] += convert_net_inflow_to_float(item['main_net_inflow'])

    latest_plate_flowin = {plate: 0 for plate in remaining_plates}
    latest_capital_sql = "SELECT name, main_net_inflow FROM a_share_fund_flow WHERE flow_date = %s"
    cursor.execute(latest_capital_sql, (latest_date, ))
    latest_capital = cursor.fetchall()
    for item in latest_capital:
        if item['name'] in plate_flowin:
            latest_plate_flowin[item['name']] = convert_net_inflow_to_float(item['main_net_inflow'])
    return plate_flowin, latest_plate_flowin



def calculate_valuation_score(remaining_plates, latest_date, cursor):
    """（基础3分）
    计算估值维度分数
            # 3.估值维度，字段，valuation_score_base
            #指标PE（2分）：PE分位P=（当前PE在近1年PE序列中的排名/近1年总交易日数）×100% 
            #P<20%：2分；20%-30%：1分；否则0分。
            #指标D（1分）：近5天跌幅D=（5天前收盘价latest_price-今日收盘价latest_price）/5天前收盘价latest_price×100% 
            #若D>8%且单日跌幅从5%收窄至2%以内：1分；否则0分。
    :param remaining_plates: 剩余板块列表
    :param latest_date: 最新日期
    :param cursor: 数据库游标
    :return: 各板块估值分数
    """
    valuation_scores = {}
    for plate in remaining_plates:
        # 获取近1年PE序列和当前PE
        query_pe = """SELECT pe_ratio FROM a_share_trading WHERE name = %s AND trade_date >= DATE_SUB(%s, INTERVAL 1 YEAR)"""
        cursor.execute(query_pe, (plate, latest_date))
        pe_data = cursor.fetchall()
        current_pe = pe_data[-1]['pe_ratio'] if pe_data else 0
        
        # 计算PE分位P
        sorted_pe = sorted([x['pe_ratio'] for x in pe_data if x['pe_ratio'] is not None])
        if sorted_pe:
            rank = sum(1 for pe in sorted_pe if pe < current_pe)
            p = (rank / len(sorted_pe)) * 100
        else:
            p = 0
        
        # 计算PE指标得分
        if p < 20:
            pe_score = 2
        elif 20 <= p <= 30:
            pe_score = 1
        else:
            pe_score = 0

        # 获取近5天收盘价数据
        query_price = """SELECT latest_price FROM a_share_fund_flow WHERE name = %s AND flow_date BETWEEN DATE_SUB(%s, INTERVAL 5 DAY) AND %s ORDER BY flow_date"""
        cursor.execute(query_price, (plate, latest_date, latest_date))
        price_data = cursor.fetchall()
        if len(price_data) >= 5:
            five_days_ago_price = float(price_data[0]['latest_price'])
            today_price = float(price_data[-1]['latest_price'])
            if five_days_ago_price != 0:
                d = ((five_days_ago_price - today_price) / five_days_ago_price) * 100
                
                # 获取单日跌幅数据
                daily_decreases = []
                for i in range(1, len(price_data)):
                    prev_price = float(price_data[i-1]['latest_price'])
                    curr_price = float(price_data[i]['latest_price'])
                    if prev_price != 0:
                        daily_decrease = ((prev_price - curr_price) / prev_price) * 100
                        daily_decreases.append(daily_decrease)
                
                # 计算指标D得分
                if d > 8 and any(decrease > 5 for decrease in daily_decreases) and all(decrease <= 2 for decrease in daily_decreases[-1:]):
                    d_score = 1
                else:
                    d_score = 0
            else:
                d_score = 0
        else:
            d_score = 0

        # 计算总估值分数（基础3分）
        valuation_scores[plate] = pe_score + d_score
    
    for plate, score in valuation_scores.items():
        update_query = "UPDATE a_share_trading SET valuation_score_base = %s WHERE name = %s AND trade_date = %s"
        cursor.execute(update_query, (score, plate, latest_date))

    cursor.connection.commit()



def calculate_volume_growth(remaining_plates, latest_date, cursor):
    """
    计算各板块的成交金额环比增幅
    :param remaining_plates: 剩余板块列表
    :param latest_date: 最新日期
    :param cursor: 数据库游标
    :return: 各板块的成交金额环比增幅列表
    """
    # 从数据库获取前一个交易日，而非使用自然日计算
    cursor.execute("SELECT MAX(trade_date) as pre_day FROM a_share_trading WHERE trade_date < %s", (latest_date,))
    yesterday = cursor.fetchone()['pre_day']
    if not yesterday:
        # 如果没有找到前一个交易日，使用默认逻辑（实际应根据业务需求处理）
        yesterday = datetime.strptime(latest_date, '%Y-%m-%d') - timedelta(days=1)
        yesterday = yesterday.strftime('%Y-%m-%d')
    volume_sql = "SELECT name, amount, trade_date FROM a_share_trading WHERE trade_date IN (%s, %s)"
    cursor.execute(volume_sql, (yesterday, latest_date))
    volume_data = cursor.fetchall()

    # 按板块组织数据
    plate_turnover = {}
    for item in volume_data:
        plate = item['name']
        amount = item['amount']
        trade_date = item['trade_date']
        if plate not in plate_turnover:
            plate_turnover[plate] = {}
        plate_turnover[plate][trade_date] = convert_net_inflow_to_float(amount)

    # 计算环比增幅
    volume_growth_list = []
    for plate, dates in plate_turnover.items():
        if yesterday in dates and latest_date in dates:
            yesterday_amount = dates[yesterday]
            today_amount = dates[latest_date]
            if yesterday_amount != 0:
                growth = (today_amount - yesterday_amount) / yesterday_amount * 100
                volume_growth_list.append((plate, growth))
    return volume_growth_list


def calculate_and_update_fund_score(remaining_plates, latest_date, cursor):
    """
    计算各板块资金分数并更新到数据库 (共4分)
    :param remaining_plates: 剩余板块列表
    :param plate_flowin: 各板块的主力净流入字典
    :param volume_growth_list: 各板块的成交金额环比增幅列表
    :param cursor: 数据库游标
    """

    # 1.1   净流入
    plate_flowin, latest_flowin = calculate_net_inflow(remaining_plates, latest_date, cursor)

     # 1.2 查询成交金额环比
    volume_growth_list = calculate_volume_growth(remaining_plates, latest_date, cursor)

    for plate in remaining_plates:
        fund_score = 0
        # 1.3.1 资金维度 (共2分)
        if plate_flowin[plate] > 10:
            if latest_flowin[plate] >=0:
                fund_score += 2
            else:
                fund_score += 1 # 在缓慢跑路
        elif plate_flowin[plate] > 5:
            if latest_flowin[plate] >=0:
                fund_score += 1
            else:
                fund_score += 0.5 # 在缓慢跑路
        elif latest_flowin[plate] < 0 or plate_flowin[plate] < 0:
            fund_score -= 1 # 主力撤退了
        else: # 这里是净流入小于5亿，并且未流出的
            fund_score = 0

        # 1.3.2 成交金额环比 (共2分)
        # 1.3.2.1 大于50%：2分，30%:1分，否则0分
        # 修正 volume_growth_list 的访问方式，假设其为字典
        volume_growth = dict(volume_growth_list).get(plate, 0)
        if volume_growth > 50:
            fund_score += 2
        elif volume_growth > 30:
            fund_score += 1

        update_fund_score_sql = "UPDATE a_share_trading SET fund_score_base = %s WHERE name = %s and trade_date = %s"
        cursor.execute(update_fund_score_sql, (fund_score, plate, latest_date))
        cursor.connection.commit()

def calculate_update_event_score(remaining_plates, latest_date, cursor):
    """ 总分3分
    计算近5天有事件的板块得分，并将事件分写入 event_score_base 字段
    :param remaining_plates: 剩余板块列表
    :param latest_date: 最新日期
    :param cursor: 数据库游标
    """
    # 计算近5天有事件的板块，每个板块得1分
    five_days_ago = datetime.strptime(latest_date, '%Y-%m-%d') - timedelta(days=5)
    event_sql = "SELECT DISTINCT industry FROM policy_events WHERE event_date >= %s AND event_date <= %s"
    cursor.execute(event_sql, (five_days_ago.strftime('%Y-%m-%d'), latest_date))

    event_sql = "SELECT * FROM policy_events WHERE event_date >= %s AND event_date <= %s"
    cursor.execute(event_sql, (five_days_ago.strftime('%Y-%m-%d'), latest_date))
    event_datas = cursor.fetchall()

    # 将事件分写入 event_score_base 字段
    try:
        for plate in remaining_plates:
            event_infos = [item for item in event_datas if item['industry'] == plate]
            event_infos_json = json.dumps(event_infos, ensure_ascii=False)
            event_score = sum(calculate_event_score(event) for event in event_infos)
            event_score = max(-2.0, min(event_score, 10))
            update_event_score_sql = "UPDATE a_share_trading SET event_infos = %s, event_score_base = %s WHERE name = %s AND trade_date = %s"
            cursor.execute(update_event_score_sql, (event_infos_json, event_score, plate, latest_date))
        
        cursor.connection.commit()
        print("事务提交成功")
    except Exception as e:
        print(f"事务提交失败: {str(e)}")
        cursor.connection.rollback()



def calculate_and_update_market_trend(latest_date, cursor):
    """
    计算指定日期的市场趋势并更新到 hs300_index 表中。

    :param latest_date: 指定日期，用于计算市场趋势。
    :param cursor: 数据库游标，用于执行 SQL 查询和更新操作。
    :return: 市场趋势字符串（牛市、熊市、震荡市），若数据不足则返回 None。
    """
    # 获取近22个交易日的日期记录
    from datetime import datetime, timedelta
    get_30days_ago_index_sql = "SELECT closing_index FROM (SELECT closing_index, index_date FROM hs300_index WHERE index_date <= %s ORDER BY index_date DESC LIMIT 22) sub ORDER BY index_date ASC LIMIT 1"
    cursor.execute(get_30days_ago_index_sql, (latest_date,))
    thirty_days_ago_index_row = cursor.fetchone()
    if not thirty_days_ago_index_row:
        return None
    thirty_days_ago_index = float(thirty_days_ago_index_row['closing_index'])

    get_current_index_sql = "SELECT closing_index FROM hs300_index WHERE index_date = %s"
    cursor.execute(get_current_index_sql, (latest_date,))
    current_index_row = cursor.fetchone()
    if not current_index_row:
        return None
    current_index = float(current_index_row['closing_index'])

    # 计算涨幅
    increase_rate = (current_index - thirty_days_ago_index) / thirty_days_ago_index

    # 判断市场趋势
    if increase_rate > 0.05:
        market_trend = '牛市'
    elif increase_rate < -0.05:
        market_trend = '熊市'
    else:
        market_trend = '震荡市'

    # 将市场趋势写入 hs300_index 表
    update_trend_sql = "UPDATE hs300_index SET market_trend = %s WHERE index_date = %s"
    cursor.execute(update_trend_sql, (market_trend, latest_date))
    return market_trend


def calculate_weighted_coefficient(latest_date, cursor):
    """
    根据给定日期计算市场趋势和权重，并返回该日期的权重值。

    :param latest_date: 指定日期，用于计算市场趋势和获取权重。
    :param cursor: 数据库游标，用于执行 SQL 查询和更新操作。
    :return: 给定日期的资金权重、事件权重和估值权重。
    """
    # 检查数据库中 market_trend 是否已存在值
    check_trend_sql = "SELECT market_trend, fund_weight, event_weight, valuation_weight FROM hs300_index WHERE index_date = %s"
    cursor.execute(check_trend_sql, (latest_date,))
    trend_row = cursor.fetchone()
    if trend_row and trend_row['market_trend']:
        return float(trend_row['fund_weight']), float(trend_row['event_weight']), float(trend_row['valuation_weight'])
    else:
        market_trend = calculate_and_update_market_trend(latest_date, cursor)
        if market_trend is None:
            raise ValueError(f"未找到 {latest_date} 的市场趋势数据，请确认HS300.XLSX中的数据完整性")

    # 2. 根据市场趋势计算权重并写入 hs300_index 表
    if market_trend == '牛市':
        fund_weight = 20
        event_weight = 3
        valuation_weight = 2
    elif market_trend == '熊市':
        fund_weight = 10
        event_weight = 2
        valuation_weight = 4
    elif market_trend == '震荡市':
        fund_weight = 10
        event_weight = 3
        valuation_weight = 3

    update_weight_sql = "UPDATE hs300_index SET fund_weight = %s, event_weight = %s, valuation_weight = %s WHERE index_date = %s"
    cursor.execute(update_weight_sql, (fund_weight, event_weight, valuation_weight, latest_date))

    return fund_weight, event_weight, valuation_weight


# 1. 定义关键词库
positive_words = [
    "合作", "战略", "创新高", "突破", "专利", "订单", "海外", 
    "扩张", "增持", "回购", "盈利", "增长", "规划", "落地"
]
negative_words = [
    "诉讼", "违规", "亏损", "下跌", "处罚", "风险", "减值", 
    "成本上涨", "需求下滑"
]

# 2. 打分函数
def calculate_event_score(event):
    # 提取事件字段
    stars = event["hot_level"]  # 如"★★★"
    title = event["title"]
    
    # （1）热门等级打分
    star_count = stars.count("★")
    star_score = 0
    if star_count == 5:
        star_score = 1
    elif star_count == 4:
        star_score = 0.5
    elif star_count == 3:
        star_score = 0.01
    else:
        star_score = 0.01
    
    # （2）关键词打分
    keyword_score = 0
    # 正向匹配
    if any(re.search(word, title) for word in positive_words):
        keyword_score = 2.0
    # 负向匹配（负向优先级高于正向）
    elif any(re.search(word, title) for word in negative_words):
        keyword_score = -2.0
    else:
        keyword_score = 1.0
    
    # 计算总分并限制区间
    total = star_score * keyword_score
    total = max(-10.0, total)
    return total

def calculate_and_update_fund_score_v2(remaining_plates, latest_date, cursor):
    """
    计算各板块资金分数并更新到数据库 (共4分)
    :param remaining_plates: 剩余板块列表
    :param plate_flowin: 各板块的主力净流入字典
    :param volume_growth_list: 各板块的成交金额环比增幅列表
    :param cursor: 数据库游标
    """
    # 1.3.1 资金维度（注意：数据中查询出来的数据默认就是亿为单位） 
    # 1.3.1.1 总市值>50000亿为大行业，大于10000亿小于55000亿为中行业，小于10000亿为小行业
    # 大行业净流入（亿） > 20：得2分， > 10得1 分， <-5得-1分，其他0分。
    # 中行业净流入（亿） > 10:得2分， >5 得1分， < -3：得-1分，其他0分。
    # 小行业净流入（亿） > 5：得2分， >3得1分， <-2得-1分，其他0分。

    # 1.3.2 换手率打分
    # 当前换手率 > 近5天平均换手率的1.5倍得1分
    # 当前换手率 > 近5天平均换手率的2倍再加1分

    # 1.3.3 涨幅与自己匹配度
    # 板块涨幅 > 1%，且净流入为正数，得1分
    # 板块涨幅 > 2%，但净流入<当天板块总成交额的3%，得-1分

    # 1.3.4 资金持续性
    # 连续2天净流入，且第二天净流入>=第一天净流入的50%，得1分
    

    # 1.3.5 成交金额环比 (共2分)
    # 1.3.5.1 大于50%：2分，30%:1分，否则0分

    # 1.3.6 成交金额环比 (共2分)
    # 1.3.6.1 大于50%：2分，30%:1分，否则0分
    # 修正 volume_growth_list 的访问方式，假设其为字典
     # 1.2 查询成交金额环比


    plate_flowin = {}
    cursor.execute("SELECT name, main_net_inflow FROM a_share_fund_flow WHERE flow_date = %s", (latest_date,))
    if cursor.rowcount == 0:
        print("未找到 {} 的主力净流入数据".format(latest_date))
        return
    plate_flowin_list = cursor.fetchall()
    for flow_data in plate_flowin_list:
        plate_flowin[flow_data['name']] = flow_data['main_net_inflow']

    volume_growth_list = calculate_volume_growth(remaining_plates, latest_date, cursor)
    #将计算出来的分数写入数据库
    for plate in remaining_plates:
        # 获取当前板块的主力净流入和成交金额环比增幅
        main_inflow = convert_net_inflow_to_float(plate_flowin[plate])
        volume_growth = dict(volume_growth_list).get(plate, 0)
        
        # 查询板块基本数据
        cursor.execute("SELECT total_market_cap, turnover_rate, daily_gain, amount FROM a_share_trading WHERE name = %s AND trade_date = %s", (plate,latest_date))
        data = cursor.fetchone()
        if not data:
            continue  # 跳过无数据的板块
        market_cap, turnover_rate, increase_rate, amount = data['total_market_cap'], data['turnover_rate'], data['daily_gain'], data['amount']
        market_cap = convert_net_inflow_to_float(market_cap)
        amount = convert_net_inflow_to_float(amount)
        increase_rate = float(increase_rate)

        # 查询近5个交易日平均换手率，使用JOIN替代IN子查询以兼容旧版MySQL
        cursor.execute("SELECT AVG(t.turnover_rate) as turnover5 FROM a_share_trading t JOIN (SELECT DISTINCT trade_date FROM a_share_trading WHERE trade_date <= %s ORDER BY trade_date DESC LIMIT 5) AS recent_dates ON t.trade_date = recent_dates.trade_date WHERE t.name = %s", (latest_date, plate))
        avg_turnover = cursor.fetchone()['turnover5'] if cursor.rowcount > 0 else 0
        
        # 查询昨日主力净流入
        cursor.execute("SELECT main_net_inflow FROM a_share_fund_flow WHERE name = %s AND flow_date = (SELECT MAX(flow_date) FROM a_share_fund_flow WHERE flow_date < %s)", (plate, latest_date))
        prev_inflow = cursor.fetchone()['main_net_inflow'] if cursor.rowcount > 0 else '0'
        prev_inflow = convert_net_inflow_to_float(prev_inflow)
        
        # 1.3.1 资金维度打分（2分）
        fund_score = 0
        if market_cap > 50000:  # 大行业
            if main_inflow > 20:
                fund_score += 2
            elif main_inflow > 10:
                fund_score += 1
            elif main_inflow < -5:
                fund_score -= 1
        elif market_cap > 10000:  # 中行业
            if main_inflow > 10:
                fund_score += 2
            elif main_inflow > 5:
                fund_score += 1
            elif main_inflow < -3:
                fund_score -= 1
        else:  # 小行业
            if main_inflow > 5:
                fund_score += 2
            elif main_inflow > 3:
                fund_score += 1
            elif main_inflow < -2:
                fund_score -= 1
        
        # 1.3.2 换手率打分（2分）
        if avg_turnover > 0:
            turnover_ratio = float(turnover_rate) / avg_turnover
            if turnover_ratio > 2:
                fund_score += 2
            elif turnover_ratio > 1.5:
                fund_score += 1
        
        # 1.3.3 涨幅与资金匹配度（1分）
        if increase_rate > 1 and main_inflow > 0:
            fund_score += 1
        if increase_rate > 2 and amount > 0 and (main_inflow / amount) < 0.03:
            fund_score -= 1
        
        # 1.3.4 资金持续性（1分）
        if main_inflow > 0 and prev_inflow > 0 and main_inflow >= prev_inflow * 0.5:
            fund_score += 1
        
        # 1.3.5 成交金额环比（2分）
        if volume_growth > 0.5:
            fund_score += 2
        elif volume_growth > 0.3:
            fund_score += 1
        
        # 更新数据库
        cursor.execute("UPDATE a_share_trading SET fund_score_base = %s WHERE name = %s AND trade_date = %s", (fund_score, plate, latest_date))
    
    # 提交事务
    cursor.connection.commit()